# -*- coding: utf-8 -*-

# @Author: Chen Hua
# @Date  : 2020-07-18
# @Desc  : 融资融券数据

import sys
import datetime
import time
import logging
import decimal
from sqlalchemy.sql.elements import and_

from db.MySqlConn import DBSession, JobConfig, engine as eg, CnRzrq
import service.job_config_service as js
from util import DateUtil
import tushare as ts
from conf.settings import TUSHARE_TOKEN

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

##tushare sdk
ts.set_token(TUSHARE_TOKEN)
pro = ts.pro_api()
##链接数据库
session = DBSession()
job_name = 'stock_cn_rzrq'

"""
详情：
https://tushare.pro/document/2?doc_id=58
"""

'''
rzye = Column(Numeric())    # 融资余额(元)
rzmre = Column(Numeric())   # 融资买入额(元)
rzche = Column(Numeric())   # 融资偿还额(元)
rqye = Column(Numeric())    # 融券余额(元)
rqmcl = Column(Numeric())   # 融券卖出量(股,份,手)
rzrqye = Column(Numeric())  # 融资融券余额(元)
rqyl = Column(Numeric())    # 融券余量(股,份,手)
'''
def process_rzrq_api(begin_dt):
    for _ in range(1):  # 重试三次
        new_ia = []
        try:
            df = pro.margin(trade_date=DateUtil.date_to_yyyyMMhh_str(begin_dt))
            if df.empty:
                print("没有数据返回，请检查是否出错NEW...")
            for index, row in df.iterrows():
                print(row)
                dt = begin_dt
                rqyl=None
                if rqyl in row:
                     rqyl=row['rqyl']
                new_ia.append( CnRzrq(dt=dt, exchange=row['exchange_id'], rzye=row['rzye'],
                                rzmre=row['rzmre'], rzche=row['rzche'],rqye=row['rqye'],
                                rqmcl=row['rqmcl'], rzrqye=row['rzrqye'],rqyl=rqyl) )
        except Exception as e:
            print(str(e))
            time.sleep(2)
        else:
            return new_ia


def stock_cn_rzrq_info_sync():
    try:
        now = datetime.datetime.now()
        begin_dt = datetime.datetime(2010, 4, 1).date()  ##2010年3月31才有这个业务
        ##从数据库获取上次已获取的数据日期
        spider_config = session.query(JobConfig).filter(JobConfig.job_name == job_name).one()
        if spider_config and spider_config.job_mode == 'inc' and spider_config.last_data_at:
            begin_dt = spider_config.last_data_at

        while begin_dt < now.date():
            ai_list = process_rzrq_api(begin_dt)
            print("成功爬取 ", str(begin_dt), "的融资融券信息...")
            if ai_list:
                for ai in ai_list:
                    session.merge(ai)
                    session.commit()
            begin_dt = begin_dt + datetime.timedelta(days=1)  ##日期各加7天
            time.sleep(2)  # 睡眠一秒，因为接口限制 100次/分钟
            js.update_job_config(job_name, begin_dt)
        print('Stock rzrq 信息已保存到表 stock_cn_rzrq ...')
    except Exception as e:
        print('获取Stock rzrq 信息失败.')
        logger.exception(e)
        sys.exit(1)
    finally:
        session.close()


if __name__ == "__main__":
    stock_cn_rzrq_info_sync()
